{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 读取列表数据\n",
    "- pd.read_csv()读入csv文档\n",
    "- 参数解析：\n",
    " 1. sep='\\t'，以\\t为数据分隔符\n",
    " 2. na_values = 'na_rep'，将na_rep识别为空值\n",
    " 3. index_col = [0,1,2]，将指定的0、1、2列设置为索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df_raw = pd.read_csv(\"fsnd_zb_data.tsv\",encoding='utf8',sep='\\t',\\\n",
    "                    keep_default_na=False,\\\n",
    "                     na_values = 'na_rep',index_col = [0,1,2])\n",
    "df_m = pd.read_csv(\"fsnd_zb_meta.tsv\",encoding='utf8',sep='\\t',\\\n",
    "                    keep_default_na=False,na_values = 'na_rap',index_col = 0)\n",
    "df_r = pd.read_csv(\"reg_treeId_level2.tsv\",encoding='utf8',sep='\\t',\\\n",
    "                    keep_default_na=False,na_values = 'na_rap',index_col = 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查看格数据的信息\n",
    "1. df.head()，查看表格前五行\n",
    "2. df.tail()，查看表格后五行\n",
    "3. df.shape()，查看表格形状\n",
    "4. df.describe()，查看表格的描述性统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zb</th>\n",
       "      <th>reg</th>\n",
       "      <th>sj</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">A010101</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">110000</th>\n",
       "      <th>2018</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     data\n",
       "zb      reg    sj        \n",
       "A010101 110000 2018   NaN\n",
       "               2017   NaN\n",
       "               2016   NaN\n",
       "               2015   NaN\n",
       "               2014   NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cname</th>\n",
       "      <th>dotcount</th>\n",
       "      <th>exp</th>\n",
       "      <th>ifshowcode</th>\n",
       "      <th>memo</th>\n",
       "      <th>name</th>\n",
       "      <th>nodesort</th>\n",
       "      <th>sortcode</th>\n",
       "      <th>tag</th>\n",
       "      <th>unit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A010101</th>\n",
       "      <td>地级区划数</td>\n",
       "      <td>0</td>\n",
       "      <td>指地级行政单位即介于省级和县级之间的一级地方行政区域的个数，包括地区、自治州、行政区和盟。</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A010102</th>\n",
       "      <td>地级市数</td>\n",
       "      <td>0</td>\n",
       "      <td>市是省、自治区内人口较集中，政治、经济、文化等方面较重要的城市。市人民政府为一级地方行政组织...</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>地级市数</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A010103</th>\n",
       "      <td>县级区划数</td>\n",
       "      <td>0</td>\n",
       "      <td>县级行政单位指中国地方二级行政区域，是地方政权的基础。县级行政单位包括县、自治县、旗、自治旗...</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>县级区划数</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A010104</th>\n",
       "      <td>市辖区数</td>\n",
       "      <td>0</td>\n",
       "      <td>市辖区（简称区）城市基层政权组织的行政区域。直辖市和较大的市多将市区范围划分为若干区，设立区...</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>市辖区数</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A010105</th>\n",
       "      <td>县级市数</td>\n",
       "      <td>0</td>\n",
       "      <td>县级市是中国大陆行政区划名称，行政地位与县相同的县级行政区</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>县级市数</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         cname  dotcount                                                exp  \\\n",
       "code                                                                          \n",
       "A010101  地级区划数         0      指地级行政单位即介于省级和县级之间的一级地方行政区域的个数，包括地区、自治州、行政区和盟。   \n",
       "A010102   地级市数         0  市是省、自治区内人口较集中，政治、经济、文化等方面较重要的城市。市人民政府为一级地方行政组织...   \n",
       "A010103  县级区划数         0  县级行政单位指中国地方二级行政区域，是地方政权的基础。县级行政单位包括县、自治县、旗、自治旗...   \n",
       "A010104   市辖区数         0  市辖区（简称区）城市基层政权组织的行政区域。直辖市和较大的市多将市区范围划分为若干区，设立区...   \n",
       "A010105   县级市数         0                      县级市是中国大陆行政区划名称，行政地位与县相同的县级行政区   \n",
       "\n",
       "         ifshowcode memo   name  nodesort  sortcode tag unit  \n",
       "code                                                          \n",
       "A010101       False       地级区划数         1         2        个  \n",
       "A010102       False        地级市数         1         3        个  \n",
       "A010103       False       县级区划数         1         4        个  \n",
       "A010104       False        市辖区数         1         5        个  \n",
       "A010105       False        县级市数         1         6        个  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dbcode</th>\n",
       "      <th>exp</th>\n",
       "      <th>id</th>\n",
       "      <th>isParent</th>\n",
       "      <th>name</th>\n",
       "      <th>open</th>\n",
       "      <th>pid</th>\n",
       "      <th>wd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>i</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>110000</td>\n",
       "      <td>True</td>\n",
       "      <td>北京市</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>120000</td>\n",
       "      <td>True</td>\n",
       "      <td>天津市</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>130000</td>\n",
       "      <td>True</td>\n",
       "      <td>河北省</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>140000</td>\n",
       "      <td>True</td>\n",
       "      <td>山西省</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>150000</td>\n",
       "      <td>True</td>\n",
       "      <td>内蒙古自治区</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  dbcode exp      id  isParent    name   open     pid   wd\n",
       "i                                                         \n",
       "0   fsnd      110000      True     北京市  False  100001  reg\n",
       "1   fsnd      120000      True     天津市  False  100001  reg\n",
       "2   fsnd      130000      True     河北省  False  100001  reg\n",
       "3   fsnd      140000      True     山西省  False  100001  reg\n",
       "4   fsnd      150000      True  内蒙古自治区  False  100001  reg"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df_raw.head()) #看头\n",
    "#display(df_raw.tail()) #看尾\n",
    "#display(df_raw.shape) #表格形状\n",
    "display(df_m.head())\n",
    "display(df_r.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 构建字典\n",
    "\n",
    "## 构建指标字典\n",
    "- 将df_m的code和cname构建为一一对应的字典\n",
    "- 在读取df_m时，已经将code设置为列表索引，这很重要\n",
    "- to_dict()将Series格式转换为一一对应的字典形式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cname</th>\n",
       "      <th>dotcount</th>\n",
       "      <th>exp</th>\n",
       "      <th>ifshowcode</th>\n",
       "      <th>memo</th>\n",
       "      <th>name</th>\n",
       "      <th>nodesort</th>\n",
       "      <th>sortcode</th>\n",
       "      <th>tag</th>\n",
       "      <th>unit</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>code</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A010101</th>\n",
       "      <td>地级区划数</td>\n",
       "      <td>0</td>\n",
       "      <td>指地级行政单位即介于省级和县级之间的一级地方行政区域的个数，包括地区、自治州、行政区和盟。</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>个</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         cname  dotcount                                            exp  \\\n",
       "code                                                                      \n",
       "A010101  地级区划数         0  指地级行政单位即介于省级和县级之间的一级地方行政区域的个数，包括地区、自治州、行政区和盟。   \n",
       "\n",
       "         ifshowcode memo   name  nodesort  sortcode tag unit  \n",
       "code                                                          \n",
       "A010101       False       地级区划数         1         2        个  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'A010101': '地级区划数',\n",
      " 'A010102': '地级市数',\n",
      " 'A010103': '县级区划数',\n",
      " 'A010104': '市辖区数',\n",
      " 'A010105': '县级市数',\n",
      " 'A010106': '县数',\n",
      " 'A010107': '自治县数',\n",
      " 'A010108': '乡镇级区划数',\n",
      " 'A010109': '镇数',\n",
      " 'A01010A': '乡数',\n",
      " 'A01010B': '街道办事处',\n",
      " 'A010201': '三次产业法人单位数',\n",
      " 'A010202': '分机构类型法人单位数',\n",
      " 'A010203': '分行业法人单位数',\n",
      " 'A010301': '按控股情况分企业法人单位数',\n",
      " 'A010302': '按登记注册类型分企业法人单位数',\n",
      " 'A020101': '地区生产总值',\n",
      " 'A020102': '第一产业增加值',\n",
      " 'A020103': '第二产业增加值',\n",
      " 'A020104': '第三产业增加值',\n",
      " 'A020105': '农林牧渔业增加值'}\n"
     ]
    }
   ],
   "source": [
    "display(df_m[0:1])\n",
    "display(type(df_m['cname']))\n",
    "指标字典 = df_m['cname'].to_dict()\n",
    "#display(指标字典) #太长了\n",
    "\n",
    "#查看指标字典前20个元素\n",
    "import pprint\n",
    "new_a = {}\n",
    "for i,(k,v) in enumerate(指标字典.items()):\n",
    "    new_a[k]=v\n",
    "    if i==20:\n",
    "        pprint.pprint(new_a)\n",
    "        break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 构建地区字典\n",
    "- 将df_r的id和name构建为一一对应的字典\n",
    "- 因为读取时，没有进行设置索引操作，这里先用set_index(\"id\")将id列设置为索引列\n",
    "- 其他步骤同上"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dbcode</th>\n",
       "      <th>exp</th>\n",
       "      <th>id</th>\n",
       "      <th>isParent</th>\n",
       "      <th>name</th>\n",
       "      <th>open</th>\n",
       "      <th>pid</th>\n",
       "      <th>wd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>i</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>110000</td>\n",
       "      <td>True</td>\n",
       "      <td>北京市</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>120000</td>\n",
       "      <td>True</td>\n",
       "      <td>天津市</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>130000</td>\n",
       "      <td>True</td>\n",
       "      <td>河北省</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>140000</td>\n",
       "      <td>True</td>\n",
       "      <td>山西省</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>fsnd</td>\n",
       "      <td></td>\n",
       "      <td>150000</td>\n",
       "      <td>True</td>\n",
       "      <td>内蒙古自治区</td>\n",
       "      <td>False</td>\n",
       "      <td>100001</td>\n",
       "      <td>reg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  dbcode exp      id  isParent    name   open     pid   wd\n",
       "i                                                         \n",
       "0   fsnd      110000      True     北京市  False  100001  reg\n",
       "1   fsnd      120000      True     天津市  False  100001  reg\n",
       "2   fsnd      130000      True     河北省  False  100001  reg\n",
       "3   fsnd      140000      True     山西省  False  100001  reg\n",
       "4   fsnd      150000      True  内蒙古自治区  False  100001  reg"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "{110000: '北京市',\n",
       " 120000: '天津市',\n",
       " 130000: '河北省',\n",
       " 140000: '山西省',\n",
       " 150000: '内蒙古自治区',\n",
       " 210000: '辽宁省',\n",
       " 220000: '吉林省',\n",
       " 230000: '黑龙江省',\n",
       " 310000: '上海市',\n",
       " 320000: '江苏省',\n",
       " 330000: '浙江省',\n",
       " 340000: '安徽省',\n",
       " 350000: '福建省',\n",
       " 360000: '江西省',\n",
       " 370000: '山东省',\n",
       " 410000: '河南省',\n",
       " 420000: '湖北省',\n",
       " 430000: '湖南省',\n",
       " 440000: '广东省',\n",
       " 450000: '广西壮族自治区',\n",
       " 460000: '海南省',\n",
       " 500000: '重庆市',\n",
       " 510000: '四川省',\n",
       " 520000: '贵州省',\n",
       " 530000: '云南省',\n",
       " 540000: '西藏自治区',\n",
       " 610000: '陕西省',\n",
       " 620000: '甘肃省',\n",
       " 630000: '青海省',\n",
       " 640000: '宁夏回族自治区',\n",
       " 650000: '新疆维吾尔自治区'}"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "地区字典 = df_r.set_index(\"id\")['name'].to_dict()\n",
    "display(df_r.head())\n",
    "display(地区字典)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 表格整合处理\n",
    "- 上面我们以字典的形式处理好了指标和地区的数据，接下来对df_raw数据进行整合\n",
    "\n",
    "## 更改指标的的index值\n",
    "- df_raw在读取时，将0、1、2列设置为了索引列，用reset_index()将起恢复原则\n",
    "- 再利用set_index(\"zb\")将zb列设置为索引列\n",
    "- 利用rename更改index的索引值名称，其中index需要为赋予字典参数\n",
    "- 最终的结果赋值于df中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zb</th>\n",
       "      <th>reg</th>\n",
       "      <th>sj</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">A010101</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">110000</th>\n",
       "      <th>2018</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     data\n",
       "zb      reg    sj        \n",
       "A010101 110000 2018   NaN\n",
       "               2017   NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>reg</th>\n",
       "      <th>sj</th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zb</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>地级区划数</th>\n",
       "      <td>110000</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地级区划数</th>\n",
       "      <td>110000</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地级区划数</th>\n",
       "      <td>110000</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地级区划数</th>\n",
       "      <td>110000</td>\n",
       "      <td>2015</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地级区划数</th>\n",
       "      <td>110000</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>城乡居民社会养老保险累计结余</th>\n",
       "      <td>650000</td>\n",
       "      <td>2013</td>\n",
       "      <td>33.856600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>城乡居民社会养老保险累计结余</th>\n",
       "      <td>650000</td>\n",
       "      <td>2012</td>\n",
       "      <td>24.914044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>城乡居民社会养老保险累计结余</th>\n",
       "      <td>650000</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>城乡居民社会养老保险累计结余</th>\n",
       "      <td>650000</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>城乡居民社会养老保险累计结余</th>\n",
       "      <td>650000</td>\n",
       "      <td>2009</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>908300 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   reg    sj       data\n",
       "zb                                     \n",
       "地级区划数           110000  2018        NaN\n",
       "地级区划数           110000  2017        NaN\n",
       "地级区划数           110000  2016        NaN\n",
       "地级区划数           110000  2015        NaN\n",
       "地级区划数           110000  2014        NaN\n",
       "...                ...   ...        ...\n",
       "城乡居民社会养老保险累计结余  650000  2013  33.856600\n",
       "城乡居民社会养老保险累计结余  650000  2012  24.914044\n",
       "城乡居民社会养老保险累计结余  650000  2011        NaN\n",
       "城乡居民社会养老保险累计结余  650000  2010        NaN\n",
       "城乡居民社会养老保险累计结余  650000  2009        NaN\n",
       "\n",
       "[908300 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df_raw[:2]) #查看df_raw[:2]的前两行\n",
    "df = df_raw.reset_index().set_index(\"zb\").rename(index=指标字典) # 指标字典为字典参数\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 更改地区的的index值\n",
    "- 步骤同时，但需要将重新设置索引列，这里是将reg设置为索引列\n",
    "- 结果再次赋予df中，更新了df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>zb</th>\n",
       "      <th>sj</th>\n",
       "      <th>data</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>reg</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2015</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆维吾尔自治区</th>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2013</td>\n",
       "      <td>33.856600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆维吾尔自治区</th>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2012</td>\n",
       "      <td>24.914044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆维吾尔自治区</th>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆维吾尔自治区</th>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>新疆维吾尔自治区</th>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2009</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>908300 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      zb    sj       data\n",
       "reg                                      \n",
       "北京市                地级区划数  2018        NaN\n",
       "北京市                地级区划数  2017        NaN\n",
       "北京市                地级区划数  2016        NaN\n",
       "北京市                地级区划数  2015        NaN\n",
       "北京市                地级区划数  2014        NaN\n",
       "...                  ...   ...        ...\n",
       "新疆维吾尔自治区  城乡居民社会养老保险累计结余  2013  33.856600\n",
       "新疆维吾尔自治区  城乡居民社会养老保险累计结余  2012  24.914044\n",
       "新疆维吾尔自治区  城乡居民社会养老保险累计结余  2011        NaN\n",
       "新疆维吾尔自治区  城乡居民社会养老保险累计结余  2010        NaN\n",
       "新疆维吾尔自治区  城乡居民社会养老保险累计结余  2009        NaN\n",
       "\n",
       "[908300 rows x 3 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#[地区字典[x] for x in set(df.reset_index()['reg'].to_list())]\n",
    "df = df.reset_index().set_index(\"reg\").rename(index=地区字典)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 更改表格的columns值\n",
    "- 更改前，需要用df.reset_index()重新设置索引列\n",
    "- 利用rename更改columns的名称，其中index需要为赋予字典参数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地区</th>\n",
       "      <th>指标</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2017</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2016</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2015</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>北京市</td>\n",
       "      <td>地级区划数</td>\n",
       "      <td>2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908295</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2013</td>\n",
       "      <td>33.856600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908296</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2012</td>\n",
       "      <td>24.914044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908297</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2011</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908298</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>908299</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>城乡居民社会养老保险累计结余</td>\n",
       "      <td>2009</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>908300 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              地区              指标     年         数据\n",
       "0            北京市           地级区划数  2018        NaN\n",
       "1            北京市           地级区划数  2017        NaN\n",
       "2            北京市           地级区划数  2016        NaN\n",
       "3            北京市           地级区划数  2015        NaN\n",
       "4            北京市           地级区划数  2014        NaN\n",
       "...          ...             ...   ...        ...\n",
       "908295  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2013  33.856600\n",
       "908296  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2012  24.914044\n",
       "908297  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2011        NaN\n",
       "908298  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2010        NaN\n",
       "908299  新疆维吾尔自治区  城乡居民社会养老保险累计结余  2009        NaN\n",
       "\n",
       "[908300 rows x 4 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_zh = df.reset_index().rename(columns={\"zb\":\"指标\",\"reg\":\"地区\",\\\n",
    "                                        \"sj\":\"年\",\"data\":\"数据\"})\n",
    "df_zh"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 数据清洗\n",
    "\n",
    "## 清洗指标\n",
    "- 如果想要取出指标包含城镇单位就业人员的所有行\n",
    "- str.contains(\"\")取出指标中包含\"城镇单位就业人员\"的所有行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>地区</th>\n",
       "      <th>指标</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>19530</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19531</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2017</td>\n",
       "      <td>812.8589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19532</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2016</td>\n",
       "      <td>791.5197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19533</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2015</td>\n",
       "      <td>777.3448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19534</th>\n",
       "      <td>北京市</td>\n",
       "      <td>城镇单位就业人员</td>\n",
       "      <td>2014</td>\n",
       "      <td>755.8601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56725</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2013</td>\n",
       "      <td>46636.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56726</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2012</td>\n",
       "      <td>45071.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56727</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2011</td>\n",
       "      <td>39862.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56728</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2010</td>\n",
       "      <td>35950.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56729</th>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>2009</td>\n",
       "      <td>31217.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>22940 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             地区                     指标     年          数据\n",
       "19530       北京市               城镇单位就业人员  2018         NaN\n",
       "19531       北京市               城镇单位就业人员  2017    812.8589\n",
       "19532       北京市               城镇单位就业人员  2016    791.5197\n",
       "19533       北京市               城镇单位就业人员  2015    777.3448\n",
       "19534       北京市               城镇单位就业人员  2014    755.8601\n",
       "...         ...                    ...   ...         ...\n",
       "56725  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2013  46636.0000\n",
       "56726  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2012  45071.0000\n",
       "56727  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2011  39862.0000\n",
       "56728  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2010  35950.0000\n",
       "56729  新疆维吾尔自治区  公共管理和社会组织城镇单位就业人员平均工资  2009  31217.0000\n",
       "\n",
       "[22940 rows x 4 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dslice = df_zh [ df_zh.指标.str.contains(\"城镇单位就业人员\")] \n",
    "dslice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查看清洗后的指标\n",
    "- unique()查看不同的指标值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['城镇单位就业人员', '农林牧渔业城镇单位就业人员', '采矿业城镇单位就业人员', '制造业城镇单位就业人员',\n",
       "       '电力、燃气及水的生产和供应业城镇单位就业人员', '建筑业城镇单位就业人员', '交通运输、仓储及邮电通信业城镇单位就业人员',\n",
       "       '信息传输、计算机服务和软件业城镇单位就业人员', '批发和零售业城镇单位就业人员', '住宿和餐饮业城镇单位就业人员',\n",
       "       '金融业城镇单位就业人员', '房地产业城镇单位就业人员', '租赁和商务服务业城镇单位就业人员',\n",
       "       '科学研究、技术服务和地质勘查业城镇单位就业人员', '水利、环境和公共设施管理业城镇单位就业人员',\n",
       "       '居民服务和其他服务业城镇单位就业人员', '教育业城镇单位就业人员', '卫生、社会保障和社会福利业城镇单位就业人员',\n",
       "       '文化、体育和娱乐业城镇单位就业人员', '公共管理和社会组织城镇单位就业人员', '城镇单位就业人员工资总额',\n",
       "       '国有城镇单位就业人员工资总额', '其他城镇单位就业人员工资总额', '城镇单位就业人员工资总额指数(上年=100)',\n",
       "       '国有城镇单位就业人员工资总额指数(上年=100)', '其他城镇单位就业人员工资总额指数(上年=100)',\n",
       "       '城镇单位就业人员平均工资', '城镇单位就业人员平均货币工资指数(上年=100)',\n",
       "       '国有城镇单位就业人员平均货币工资指数(上年=100)', '其他城镇单位就业人员平均货币工资指数(上年=100)',\n",
       "       '城镇单位就业人员平均实际工资指数(上年=100)', '国有城镇单位就业人员平均实际工资指数(上年=100)',\n",
       "       '其他城镇单位就业人员平均实际工资指数(上年=100)', '农、林、牧、渔业城镇单位就业人员工资总额',\n",
       "       '采矿业城镇单位就业人员工资总额', '制造业城镇单位就业人员工资总额', '电力、燃气及水的生产和供应业城镇单位就业人员工资总额',\n",
       "       '建筑业城镇单位就业人员工资总额', '交通运输、仓储和邮政业城镇单位就业人员工资总额',\n",
       "       '信息传输、计算机服务和软件业城镇单位就业人员工资总额', '批发和零售业城镇单位就业人员工资总额',\n",
       "       '住宿和餐饮业城镇单位就业人员工资总额', '金融业城镇单位就业人员工资总额', '房地产业城镇单位就业人员工资总额',\n",
       "       '租赁和商务服务业城镇单位就业人员工资总额', '科学研究、技术服务和地质勘查业城镇单位就业人员工资总额',\n",
       "       '水利、环境和公共设施管理业城镇单位就业人员工资总额', '居民服务和其他服务业城镇单位就业人员工资总额',\n",
       "       '教育城镇单位就业人员工资总额', '卫生、社会保障和社会福利业城镇单位就业人员工资总额',\n",
       "       '文化、体育和娱乐业城镇单位就业人员工资总额', '公共管理和社会组织城镇单位就业人员工资总额',\n",
       "       '农、林、牧、渔业城镇单位就业人员平均工资', '采矿业城镇单位就业人员平均工资', '制造业城镇单位就业人员平均工资',\n",
       "       '电力、燃气及水的生产和供应业城镇单位就业人员平均工资', '建筑业城镇单位就业人员平均工资',\n",
       "       '交通运输、仓储和邮政业城镇单位就业人员平均工资', '信息传输、计算机服务和软件业城镇单位就业人员平均工资',\n",
       "       '批发和零售业城镇单位就业人员平均工资', '住宿和餐饮业城镇单位就业人员平均工资', '金融业城镇单位就业人员平均工资',\n",
       "       '房地产业城镇单位就业人员平均工资', '租赁和商务服务业城镇单位就业人员平均工资',\n",
       "       '科学研究、技术服务和地质勘查业城镇单位就业人员平均工资', '水利、环境和公共设施管理业城镇单位就业人员平均工资',\n",
       "       '居民服务和其他服务业城镇单位就业人员平均工资', '教育城镇单位就业人员平均工资',\n",
       "       '卫生、社会保障和社会福利业城镇单位就业人员平均工资', '文化、体育和娱乐业城镇单位就业人员平均工资',\n",
       "       '公共管理和社会组织城镇单位就业人员平均工资'], dtype=object)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "指标分的可能性 = dslice.指标.unique()\n",
    "指标分的可能性"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 清洗出职位和职位指标\n",
    "- 观察上面'指标分的可能性'，可以发现'职位'和'职位指标'中间都以'城镇单位就业'为间隔\n",
    "- 利用list.split()将'指标分的可能性'分割为两部分的列表元素，既'职位'和'职位指标'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['', '人员'],\n",
       " ['农林牧渔业', '人员'],\n",
       " ['采矿业', '人员'],\n",
       " ['制造业', '人员'],\n",
       " ['电力、燃气及水的生产和供应业', '人员'],\n",
       " ['建筑业', '人员'],\n",
       " ['交通运输、仓储及邮电通信业', '人员'],\n",
       " ['信息传输、计算机服务和软件业', '人员'],\n",
       " ['批发和零售业', '人员'],\n",
       " ['住宿和餐饮业', '人员'],\n",
       " ['金融业', '人员'],\n",
       " ['房地产业', '人员'],\n",
       " ['租赁和商务服务业', '人员'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员'],\n",
       " ['水利、环境和公共设施管理业', '人员'],\n",
       " ['居民服务和其他服务业', '人员'],\n",
       " ['教育业', '人员'],\n",
       " ['卫生、社会保障和社会福利业', '人员'],\n",
       " ['文化、体育和娱乐业', '人员'],\n",
       " ['公共管理和社会组织', '人员'],\n",
       " ['', '人员工资总额'],\n",
       " ['国有', '人员工资总额'],\n",
       " ['其他', '人员工资总额'],\n",
       " ['', '人员工资总额指数(上年=100)'],\n",
       " ['国有', '人员工资总额指数(上年=100)'],\n",
       " ['其他', '人员工资总额指数(上年=100)'],\n",
       " ['', '人员平均工资'],\n",
       " ['', '人员平均货币工资指数(上年=100)'],\n",
       " ['国有', '人员平均货币工资指数(上年=100)'],\n",
       " ['其他', '人员平均货币工资指数(上年=100)'],\n",
       " ['', '人员平均实际工资指数(上年=100)'],\n",
       " ['国有', '人员平均实际工资指数(上年=100)'],\n",
       " ['其他', '人员平均实际工资指数(上年=100)'],\n",
       " ['农、林、牧、渔业', '人员工资总额'],\n",
       " ['采矿业', '人员工资总额'],\n",
       " ['制造业', '人员工资总额'],\n",
       " ['电力、燃气及水的生产和供应业', '人员工资总额'],\n",
       " ['建筑业', '人员工资总额'],\n",
       " ['交通运输、仓储和邮政业', '人员工资总额'],\n",
       " ['信息传输、计算机服务和软件业', '人员工资总额'],\n",
       " ['批发和零售业', '人员工资总额'],\n",
       " ['住宿和餐饮业', '人员工资总额'],\n",
       " ['金融业', '人员工资总额'],\n",
       " ['房地产业', '人员工资总额'],\n",
       " ['租赁和商务服务业', '人员工资总额'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员工资总额'],\n",
       " ['水利、环境和公共设施管理业', '人员工资总额'],\n",
       " ['居民服务和其他服务业', '人员工资总额'],\n",
       " ['教育', '人员工资总额'],\n",
       " ['卫生、社会保障和社会福利业', '人员工资总额'],\n",
       " ['文化、体育和娱乐业', '人员工资总额'],\n",
       " ['公共管理和社会组织', '人员工资总额'],\n",
       " ['农、林、牧、渔业', '人员平均工资'],\n",
       " ['采矿业', '人员平均工资'],\n",
       " ['制造业', '人员平均工资'],\n",
       " ['电力、燃气及水的生产和供应业', '人员平均工资'],\n",
       " ['建筑业', '人员平均工资'],\n",
       " ['交通运输、仓储和邮政业', '人员平均工资'],\n",
       " ['信息传输、计算机服务和软件业', '人员平均工资'],\n",
       " ['批发和零售业', '人员平均工资'],\n",
       " ['住宿和餐饮业', '人员平均工资'],\n",
       " ['金融业', '人员平均工资'],\n",
       " ['房地产业', '人员平均工资'],\n",
       " ['租赁和商务服务业', '人员平均工资'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员平均工资'],\n",
       " ['水利、环境和公共设施管理业', '人员平均工资'],\n",
       " ['居民服务和其他服务业', '人员平均工资'],\n",
       " ['教育', '人员平均工资'],\n",
       " ['卫生、社会保障和社会福利业', '人员平均工资'],\n",
       " ['文化、体育和娱乐业', '人员平均工资'],\n",
       " ['公共管理和社会组织', '人员平均工资']]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "指标分的可能性 = [ x.split(\"城镇单位就业\") for x in dslice.指标.unique()]\n",
    "指标分的可能性"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 根据职位指标再清洗\n",
    "- 我们需要取出'职位指标'中值为'人员平均工资'或'人员'的所有行，并且不能为空值！\n",
    "- 循环列表的格式为for (x,y) in [['x','y'],['x','y']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['', '人员'], ['农林牧渔业', '人员'], ['采矿业', '人员']]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "[['农林牧渔业', '人员'],\n",
       " ['采矿业', '人员'],\n",
       " ['制造业', '人员'],\n",
       " ['电力、燃气及水的生产和供应业', '人员'],\n",
       " ['建筑业', '人员'],\n",
       " ['交通运输、仓储及邮电通信业', '人员'],\n",
       " ['信息传输、计算机服务和软件业', '人员'],\n",
       " ['批发和零售业', '人员'],\n",
       " ['住宿和餐饮业', '人员'],\n",
       " ['金融业', '人员'],\n",
       " ['房地产业', '人员'],\n",
       " ['租赁和商务服务业', '人员'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员'],\n",
       " ['水利、环境和公共设施管理业', '人员'],\n",
       " ['居民服务和其他服务业', '人员'],\n",
       " ['教育业', '人员'],\n",
       " ['卫生、社会保障和社会福利业', '人员'],\n",
       " ['文化、体育和娱乐业', '人员'],\n",
       " ['公共管理和社会组织', '人员'],\n",
       " ['农、林、牧、渔业', '人员平均工资'],\n",
       " ['采矿业', '人员平均工资'],\n",
       " ['制造业', '人员平均工资'],\n",
       " ['电力、燃气及水的生产和供应业', '人员平均工资'],\n",
       " ['建筑业', '人员平均工资'],\n",
       " ['交通运输、仓储和邮政业', '人员平均工资'],\n",
       " ['信息传输、计算机服务和软件业', '人员平均工资'],\n",
       " ['批发和零售业', '人员平均工资'],\n",
       " ['住宿和餐饮业', '人员平均工资'],\n",
       " ['金融业', '人员平均工资'],\n",
       " ['房地产业', '人员平均工资'],\n",
       " ['租赁和商务服务业', '人员平均工资'],\n",
       " ['科学研究、技术服务和地质勘查业', '人员平均工资'],\n",
       " ['水利、环境和公共设施管理业', '人员平均工资'],\n",
       " ['居民服务和其他服务业', '人员平均工资'],\n",
       " ['教育', '人员平均工资'],\n",
       " ['卫生、社会保障和社会福利业', '人员平均工资'],\n",
       " ['文化、体育和娱乐业', '人员平均工资'],\n",
       " ['公共管理和社会组织', '人员平均工资']]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display(指标分的可能性[:3])# 观察'指标分的可能性'的情况\n",
    "指标分的可能性_取 = [[x,y] for (x,y) in 指标分的可能性 if (y=='人员平均工资' or y =='人员') and x!='']\n",
    "指标分的可能性_取"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 拼接职位指标数据\n",
    "- join(x)可以将一个列表中的两个元素用x将其拼接为一个元素，如：['x','z'].join('y')结果为['xyz']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['农林牧渔业城镇单位就业人员',\n",
       " '采矿业城镇单位就业人员',\n",
       " '制造业城镇单位就业人员',\n",
       " '电力、燃气及水的生产和供应业城镇单位就业人员',\n",
       " '建筑业城镇单位就业人员',\n",
       " '交通运输、仓储及邮电通信业城镇单位就业人员',\n",
       " '信息传输、计算机服务和软件业城镇单位就业人员',\n",
       " '批发和零售业城镇单位就业人员',\n",
       " '住宿和餐饮业城镇单位就业人员',\n",
       " '金融业城镇单位就业人员',\n",
       " '房地产业城镇单位就业人员',\n",
       " '租赁和商务服务业城镇单位就业人员',\n",
       " '科学研究、技术服务和地质勘查业城镇单位就业人员',\n",
       " '水利、环境和公共设施管理业城镇单位就业人员',\n",
       " '居民服务和其他服务业城镇单位就业人员',\n",
       " '教育业城镇单位就业人员',\n",
       " '卫生、社会保障和社会福利业城镇单位就业人员',\n",
       " '文化、体育和娱乐业城镇单位就业人员',\n",
       " '公共管理和社会组织城镇单位就业人员',\n",
       " '农、林、牧、渔业城镇单位就业人员平均工资',\n",
       " '采矿业城镇单位就业人员平均工资',\n",
       " '制造业城镇单位就业人员平均工资',\n",
       " '电力、燃气及水的生产和供应业城镇单位就业人员平均工资',\n",
       " '建筑业城镇单位就业人员平均工资',\n",
       " '交通运输、仓储和邮政业城镇单位就业人员平均工资',\n",
       " '信息传输、计算机服务和软件业城镇单位就业人员平均工资',\n",
       " '批发和零售业城镇单位就业人员平均工资',\n",
       " '住宿和餐饮业城镇单位就业人员平均工资',\n",
       " '金融业城镇单位就业人员平均工资',\n",
       " '房地产业城镇单位就业人员平均工资',\n",
       " '租赁和商务服务业城镇单位就业人员平均工资',\n",
       " '科学研究、技术服务和地质勘查业城镇单位就业人员平均工资',\n",
       " '水利、环境和公共设施管理业城镇单位就业人员平均工资',\n",
       " '居民服务和其他服务业城镇单位就业人员平均工资',\n",
       " '教育城镇单位就业人员平均工资',\n",
       " '卫生、社会保障和社会福利业城镇单位就业人员平均工资',\n",
       " '文化、体育和娱乐业城镇单位就业人员平均工资',\n",
       " '公共管理和社会组织城镇单位就业人员平均工资']"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "指标分的可能性_取_all = [\"城镇单位就业\".join(x) for x in 指标分的可能性_取]\n",
    "指标分的可能性_取_all"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 取出职位指标所在的所有行\n",
    "- 通过向loc[]传入职位指标的列表数据，返回以上职位指标所在的所有行\n",
    "- 首先需要将指标通过set_index()设置为索引列\n",
    "- 最后通过reset_index()复原索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指标</th>\n",
       "      <th>地区</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2017</td>\n",
       "      <td>3.4116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2016</td>\n",
       "      <td>3.6867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2015</td>\n",
       "      <td>3.8949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2014</td>\n",
       "      <td>3.2331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11775</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2013</td>\n",
       "      <td>46636.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11776</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2012</td>\n",
       "      <td>45071.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11777</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2011</td>\n",
       "      <td>39862.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11778</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2010</td>\n",
       "      <td>35950.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11779</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2009</td>\n",
       "      <td>31217.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11780 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          指标        地区     年          数据\n",
       "0              农林牧渔业城镇单位就业人员       北京市  2018         NaN\n",
       "1              农林牧渔业城镇单位就业人员       北京市  2017      3.4116\n",
       "2              农林牧渔业城镇单位就业人员       北京市  2016      3.6867\n",
       "3              农林牧渔业城镇单位就业人员       北京市  2015      3.8949\n",
       "4              农林牧渔业城镇单位就业人员       北京市  2014      3.2331\n",
       "...                      ...       ...   ...         ...\n",
       "11775  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2013  46636.0000\n",
       "11776  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2012  45071.0000\n",
       "11777  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2011  39862.0000\n",
       "11778  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2010  35950.0000\n",
       "11779  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2009  31217.0000\n",
       "\n",
       "[11780 rows x 4 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_就业切片 = df_zh.set_index(\"指标\").loc[指标分的可能性_取_all].reset_index()# loc[]接受名称索引\n",
    "df_就业切片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指标</th>\n",
       "      <th>地区</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>11780</td>\n",
       "      <td>11780</td>\n",
       "      <td>11780.000000</td>\n",
       "      <td>10591.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>38</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>建筑业城镇单位就业人员</td>\n",
       "      <td>黑龙江省</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>310</td>\n",
       "      <td>380</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013.500000</td>\n",
       "      <td>25995.610415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.872403</td>\n",
       "      <td>31837.310363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009.000000</td>\n",
       "      <td>0.024900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011.000000</td>\n",
       "      <td>11.323850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013.500000</td>\n",
       "      <td>11478.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016.000000</td>\n",
       "      <td>46218.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018.000000</td>\n",
       "      <td>253637.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 指标     地区             年             数据\n",
       "count         11780  11780  11780.000000   10591.000000\n",
       "unique           38     31           NaN            NaN\n",
       "top     建筑业城镇单位就业人员   黑龙江省           NaN            NaN\n",
       "freq            310    380           NaN            NaN\n",
       "mean            NaN    NaN   2013.500000   25995.610415\n",
       "std             NaN    NaN      2.872403   31837.310363\n",
       "min             NaN    NaN   2009.000000       0.024900\n",
       "25%             NaN    NaN   2011.000000      11.323850\n",
       "50%             NaN    NaN   2013.500000   11478.000000\n",
       "75%             NaN    NaN   2016.000000   46218.500000\n",
       "max             NaN    NaN   2018.000000  253637.000000"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_就业切片.describe(include=\"all\") #查看描述性统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 层次化统计\n",
    "\n",
    "## 多层次数据处理\n",
    "- 通过对df['行业']和df['行业指标']赋值，来实现添加新的列\n",
    "- 通过split()以\"城镇单位就业\"为中心进行分割，分割为'行业'和'行业指标'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['农林牧渔业', '人员'], ['农林牧渔业', '人员']]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>指标</th>\n",
       "      <th>地区</th>\n",
       "      <th>年</th>\n",
       "      <th>数据</th>\n",
       "      <th>行业</th>\n",
       "      <th>行业指标</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2017</td>\n",
       "      <td>3.4116</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2016</td>\n",
       "      <td>3.6867</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2015</td>\n",
       "      <td>3.8949</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>农林牧渔业城镇单位就业人员</td>\n",
       "      <td>北京市</td>\n",
       "      <td>2014</td>\n",
       "      <td>3.2331</td>\n",
       "      <td>农林牧渔业</td>\n",
       "      <td>人员</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11775</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2013</td>\n",
       "      <td>46636.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11776</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2012</td>\n",
       "      <td>45071.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11777</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2011</td>\n",
       "      <td>39862.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11778</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2010</td>\n",
       "      <td>35950.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11779</th>\n",
       "      <td>公共管理和社会组织城镇单位就业人员平均工资</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>2009</td>\n",
       "      <td>31217.0000</td>\n",
       "      <td>公共管理和社会组织</td>\n",
       "      <td>人员平均工资</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11780 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          指标        地区     年          数据         行业    行业指标\n",
       "0              农林牧渔业城镇单位就业人员       北京市  2018         NaN      农林牧渔业      人员\n",
       "1              农林牧渔业城镇单位就业人员       北京市  2017      3.4116      农林牧渔业      人员\n",
       "2              农林牧渔业城镇单位就业人员       北京市  2016      3.6867      农林牧渔业      人员\n",
       "3              农林牧渔业城镇单位就业人员       北京市  2015      3.8949      农林牧渔业      人员\n",
       "4              农林牧渔业城镇单位就业人员       北京市  2014      3.2331      农林牧渔业      人员\n",
       "...                      ...       ...   ...         ...        ...     ...\n",
       "11775  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2013  46636.0000  公共管理和社会组织  人员平均工资\n",
       "11776  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2012  45071.0000  公共管理和社会组织  人员平均工资\n",
       "11777  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2011  39862.0000  公共管理和社会组织  人员平均工资\n",
       "11778  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2010  35950.0000  公共管理和社会组织  人员平均工资\n",
       "11779  公共管理和社会组织城镇单位就业人员平均工资  新疆维吾尔自治区  2009  31217.0000  公共管理和社会组织  人员平均工资\n",
       "\n",
       "[11780 rows x 6 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "display([x.split(\"城镇单位就业\") for x in df_就业切片.指标][:2]) #查看列表推导的部分结果\n",
    "df_就业切片['行业'] = [x.split(\"城镇单位就业\")[0] for x in df_就业切片.指标] #用[0]来取行业\n",
    "df_就业切片['行业指标'] = [x.split(\"城镇单位就业\")[1] for x in df_就业切片.指标] #用[1]来取行业指标\n",
    "df_就业切片"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 多层次数据统计\n",
    "- 这里想要将地区作为做第一层索引，观察每个市其行业的数据情况，顺序为地区、行业、行业指标\n",
    "- 利用groupby来进行层次化的索引，其参数为具有顺序性的列表\n",
    "- agg添加统计方式，其值为只字典格式，方法有min、mean、max等"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>mean</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th>行业</th>\n",
       "      <th>行业指标</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">上海市</th>\n",
       "      <th>交通运输、仓储及邮电通信业</th>\n",
       "      <th>人员</th>\n",
       "      <td>35.6329</td>\n",
       "      <td>45.014167</td>\n",
       "      <td>51.4541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>交通运输、仓储和邮政业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>49847.0000</td>\n",
       "      <td>81817.888889</td>\n",
       "      <td>116763.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">住宿和餐饮业</th>\n",
       "      <th>人员</th>\n",
       "      <td>10.8191</td>\n",
       "      <td>19.851000</td>\n",
       "      <td>25.5494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>29564.0000</td>\n",
       "      <td>45158.777778</td>\n",
       "      <td>60153.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业</th>\n",
       "      <th>人员</th>\n",
       "      <td>6.5150</td>\n",
       "      <td>17.706767</td>\n",
       "      <td>30.7312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">黑龙江省</th>\n",
       "      <th>租赁和商务服务业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>21157.0000</td>\n",
       "      <td>38601.444444</td>\n",
       "      <td>56493.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">采矿业</th>\n",
       "      <th>人员</th>\n",
       "      <td>25.5592</td>\n",
       "      <td>36.610556</td>\n",
       "      <td>43.4887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>33417.0000</td>\n",
       "      <td>51564.777778</td>\n",
       "      <td>68926.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">金融业</th>\n",
       "      <th>人员</th>\n",
       "      <td>13.4821</td>\n",
       "      <td>17.099533</td>\n",
       "      <td>22.6548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>37056.0000</td>\n",
       "      <td>55194.888889</td>\n",
       "      <td>66790.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1178 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    数据                           \n",
       "                                   min          mean          max\n",
       "地区   行业             行业指标                                         \n",
       "上海市  交通运输、仓储及邮电通信业  人员         35.6329     45.014167      51.4541\n",
       "     交通运输、仓储和邮政业    人员平均工资  49847.0000  81817.888889  116763.0000\n",
       "     住宿和餐饮业         人员         10.8191     19.851000      25.5494\n",
       "                    人员平均工资  29564.0000  45158.777778   60153.0000\n",
       "     信息传输、计算机服务和软件业 人员          6.5150     17.706767      30.7312\n",
       "...                                ...           ...          ...\n",
       "黑龙江省 租赁和商务服务业       人员平均工资  21157.0000  38601.444444   56493.0000\n",
       "     采矿业            人员         25.5592     36.610556      43.4887\n",
       "                    人员平均工资  33417.0000  51564.777778   68926.0000\n",
       "     金融业            人员         13.4821     17.099533      22.6548\n",
       "                    人员平均工资  37056.0000  55194.888889   66790.0000\n",
       "\n",
       "[1178 rows x 3 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_就业切片.groupby(['地区','行业','行业指标']).agg({\"数据\":[\"min\",\"mean\",\"max\"]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 统一行业字符\n",
    "- 首先需要通过观察来发现不统一的行业字符串，然后再对其进行字典的创建\n",
    "- 除此之外，更改行业index的值需要对行业进行索引的设置\n",
    "- 然后在通过rename(index = 字典)来实现更改"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['农林牧渔业', '采矿业', '制造业', '电力、燃气及水的生产和供应业', '建筑业', '交通运输、仓储和邮政业',\n",
       "       '信息传输、计算机服务和软件业', '批发和零售业', '住宿和餐饮业', '金融业', '房地产业', '租赁和商务服务业',\n",
       "       '科学研究、技术服务和地质勘查业', '水利、环境和公共设施管理业', '居民服务和其他服务业', '教育业',\n",
       "       '卫生、社会保障和社会福利业', '文化、体育和娱乐业', '公共管理和社会组织'], dtype=object)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>mean</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th>行业</th>\n",
       "      <th>行业指标</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">上海市</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">交通运输、仓储和邮政业</th>\n",
       "      <th>人员</th>\n",
       "      <td>35.6329</td>\n",
       "      <td>45.014167</td>\n",
       "      <td>51.4541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>49847.0000</td>\n",
       "      <td>81817.888889</td>\n",
       "      <td>116763.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">住宿和餐饮业</th>\n",
       "      <th>人员</th>\n",
       "      <td>10.8191</td>\n",
       "      <td>19.851000</td>\n",
       "      <td>25.5494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>29564.0000</td>\n",
       "      <td>45158.777778</td>\n",
       "      <td>60153.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业</th>\n",
       "      <th>人员</th>\n",
       "      <td>6.5150</td>\n",
       "      <td>17.706767</td>\n",
       "      <td>30.7312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">黑龙江省</th>\n",
       "      <th>租赁和商务服务业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>21157.0000</td>\n",
       "      <td>38601.444444</td>\n",
       "      <td>56493.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">采矿业</th>\n",
       "      <th>人员</th>\n",
       "      <td>25.5592</td>\n",
       "      <td>36.610556</td>\n",
       "      <td>43.4887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>33417.0000</td>\n",
       "      <td>51564.777778</td>\n",
       "      <td>68926.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">金融业</th>\n",
       "      <th>人员</th>\n",
       "      <td>13.4821</td>\n",
       "      <td>17.099533</td>\n",
       "      <td>22.6548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>37056.0000</td>\n",
       "      <td>55194.888889</td>\n",
       "      <td>66790.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1178 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    数据                           \n",
       "                                   min          mean          max\n",
       "地区   行业             行业指标                                         \n",
       "上海市  交通运输、仓储和邮政业    人员         35.6329     45.014167      51.4541\n",
       "                    人员平均工资  49847.0000  81817.888889  116763.0000\n",
       "     住宿和餐饮业         人员         10.8191     19.851000      25.5494\n",
       "                    人员平均工资  29564.0000  45158.777778   60153.0000\n",
       "     信息传输、计算机服务和软件业 人员          6.5150     17.706767      30.7312\n",
       "...                                ...           ...          ...\n",
       "黑龙江省 租赁和商务服务业       人员平均工资  21157.0000  38601.444444   56493.0000\n",
       "     采矿业            人员         25.5592     36.610556      43.4887\n",
       "                    人员平均工资  33417.0000  51564.777778   68926.0000\n",
       "     金融业            人员         13.4821     17.099533      22.6548\n",
       "                    人员平均工资  37056.0000  55194.888889   66790.0000\n",
       "\n",
       "[1178 rows x 3 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "统一字词 = {\"交通运输、仓储及邮电通信业\":\"交通运输、仓储和邮政业\",\\\n",
    "            \"农、林、牧、渔业\":\"农林牧渔业\",\\\n",
    "            \"教育\":\"教育业\"}\n",
    "df_就业切片_新 = df_就业切片.set_index('行业').rename(index = 统一字词).reset_index()\n",
    "display(df_就业切片_新.行业.unique()) #查看新数据行业中的不同值\n",
    "df_就业切片_新.groupby(['地区','行业','行业指标']).agg({\"数据\":[\"min\",\"mean\",\"max\"]}) #再次进行层次化统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 多层次统计平均工资 \n",
    "- 首先需要取出行业指标为'人员平均工资'的所有行\n",
    "- 一般来说如果进行行挑选，可以进行的操作是：df_就业切片_新[df_就业切片_新.行业指标=='人员平均工资']\n",
    "- 但利用df_就业切片_新.query(\"行业指标=='人员平均工资'\")更为优雅，效果一致"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>mean</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>地区</th>\n",
       "      <th>行业</th>\n",
       "      <th>行业指标</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">上海市</th>\n",
       "      <th>交通运输、仓储和邮政业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>49847.0</td>\n",
       "      <td>81817.888889</td>\n",
       "      <td>116763.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>住宿和餐饮业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>29564.0</td>\n",
       "      <td>45158.777778</td>\n",
       "      <td>60153.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>信息传输、计算机服务和软件业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>101367.0</td>\n",
       "      <td>153913.666667</td>\n",
       "      <td>212063.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>公共管理和社会组织</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>65919.0</td>\n",
       "      <td>92678.222222</td>\n",
       "      <td>118964.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>农林牧渔业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>38093.0</td>\n",
       "      <td>54100.666667</td>\n",
       "      <td>69903.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">黑龙江省</th>\n",
       "      <th>电力、燃气及水的生产和供应业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>32767.0</td>\n",
       "      <td>51645.000000</td>\n",
       "      <td>68215.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>科学研究、技术服务和地质勘查业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>39938.0</td>\n",
       "      <td>57595.444444</td>\n",
       "      <td>73978.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>租赁和商务服务业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>21157.0</td>\n",
       "      <td>38601.444444</td>\n",
       "      <td>56493.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>采矿业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>33417.0</td>\n",
       "      <td>51564.777778</td>\n",
       "      <td>68926.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业</th>\n",
       "      <th>人员平均工资</th>\n",
       "      <td>37056.0</td>\n",
       "      <td>55194.888889</td>\n",
       "      <td>66790.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>589 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   数据                         \n",
       "                                  min           mean       max\n",
       "地区   行业              行业指标                                     \n",
       "上海市  交通运输、仓储和邮政业     人员平均工资   49847.0   81817.888889  116763.0\n",
       "     住宿和餐饮业          人员平均工资   29564.0   45158.777778   60153.0\n",
       "     信息传输、计算机服务和软件业  人员平均工资  101367.0  153913.666667  212063.0\n",
       "     公共管理和社会组织       人员平均工资   65919.0   92678.222222  118964.0\n",
       "     农林牧渔业           人员平均工资   38093.0   54100.666667   69903.0\n",
       "...                               ...            ...       ...\n",
       "黑龙江省 电力、燃气及水的生产和供应业  人员平均工资   32767.0   51645.000000   68215.0\n",
       "     科学研究、技术服务和地质勘查业 人员平均工资   39938.0   57595.444444   73978.0\n",
       "     租赁和商务服务业        人员平均工资   21157.0   38601.444444   56493.0\n",
       "     采矿业             人员平均工资   33417.0   51564.777778   68926.0\n",
       "     金融业             人员平均工资   37056.0   55194.888889   66790.0\n",
       "\n",
       "[589 rows x 3 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "人员平均工资 = df_就业切片_新.query(\"行业指标=='人员平均工资'\")\n",
    "人员平均工资.groupby(['地区','行业','行业指标']).agg({\"数据\":[\"min\",\"mean\",\"max\"]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 多层次统计平均工资排序 \n",
    "- 在这里，我们排序使用.sort_values(by = \"排序的变数\",ascending = False),False代表又大到小"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>数据</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>行业</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">金融业</th>\n",
       "      <th>北京市</th>\n",
       "      <td>204188.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>上海市</th>\n",
       "      <td>188385.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">信息传输、计算机服务和软件业</th>\n",
       "      <th>上海市</th>\n",
       "      <td>153913.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>北京市</th>\n",
       "      <td>139098.888889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>金融业</th>\n",
       "      <th>西藏自治区</th>\n",
       "      <td>133585.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>水利、环境和公共设施管理业</th>\n",
       "      <th>山西省</th>\n",
       "      <td>23166.111111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">农林牧渔业</th>\n",
       "      <th>西藏自治区</th>\n",
       "      <td>22214.888889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>黑龙江省</th>\n",
       "      <td>21949.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>河北省</th>\n",
       "      <td>16067.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>辽宁省</th>\n",
       "      <td>12813.111111</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>589 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 数据\n",
       "行业             地区                  \n",
       "金融业            北京市    204188.555556\n",
       "               上海市    188385.000000\n",
       "信息传输、计算机服务和软件业 上海市    153913.666667\n",
       "               北京市    139098.888889\n",
       "金融业            西藏自治区  133585.222222\n",
       "...                             ...\n",
       "水利、环境和公共设施管理业  山西省     23166.111111\n",
       "农林牧渔业          西藏自治区   22214.888889\n",
       "               黑龙江省    21949.555556\n",
       "               河北省     16067.333333\n",
       "               辽宁省     12813.111111\n",
       "\n",
       "[589 rows x 1 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "人员平均工资.groupby(['行业','地区']).agg({\"数据\":\"mean\"}).sort_values(by = \"数据\",ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([('数据',  'min'),\n",
       "            ('数据', 'mean'),\n",
       "            ('数据',  'max')],\n",
       "           )"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "人员平均工资.groupby(['行业']).agg({\"数据\":[\"min\",\"mean\",\"max\"]}).columns # 查看columns的层级结构"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 读出报表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "报表 = 人员平均工资.groupby(['行业','地区']).agg({\"数据\":\"mean\"}).sort_values(by = \"数据\",ascending = False)\n",
    "报表.to_csv(\"报表.tsv\",encoding=\"utf8\",sep=\"\\t\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "299.2px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
